{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import MySQLdb as mdb\n",
    "\n",
    "import matplotlib\n",
    "matplotlib.use(\"TkAgg\")\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from matplotlib.collections import LineCollection\n",
    "\n",
    "import pandas as pd\n",
    "from sklearn import cluster,covariance,manifold\n",
    "\n",
    "from matplotlib.font_manager import FontProperties\n",
    "\n",
    "\n",
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api('579f438eb79d5ee41e68586e4adf0dfc11edb58d68b6843e9d843518')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5396 entries, 0 to 5395\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5396 non-null   object\n",
      " 1   symbol        5396 non-null   object\n",
      " 2   name          5396 non-null   object\n",
      " 3   area          5380 non-null   object\n",
      " 4   industry      5380 non-null   object\n",
      " 5   cnspell       5396 non-null   object\n",
      " 6   market        5396 non-null   object\n",
      " 7   list_date     5396 non-null   object\n",
      " 8   act_name      2575 non-null   object\n",
      " 9   act_ent_type  2575 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.7+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ts_code         5396\n",
       "symbol          5396\n",
       "name            5396\n",
       "area            5380\n",
       "industry        5380\n",
       "cnspell         5396\n",
       "market          5396\n",
       "list_date       5396\n",
       "act_name        2575\n",
       "act_ent_type    2575\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pro.query('stock_basic', exchange='',list_status='L')\n",
    "data.info()\n",
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5396"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:limuze@localhost/limuze')\n",
    "\n",
    "data.to_sql('limuze', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'stocks'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[17], line 4\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mdjango\u001b[39;00m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[1;32m----> 4\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mstocks\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmodels\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m StockBasic\n\u001b[0;32m      6\u001b[0m \u001b[38;5;66;03m# 配置 Django 环境\u001b[39;00m\n\u001b[0;32m      7\u001b[0m os\u001b[38;5;241m.\u001b[39menviron\u001b[38;5;241m.\u001b[39msetdefault(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mDJANGO_SETTINGS_MODULE\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mstock_project.settings\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'stocks'"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import django\n",
    "import pandas as pd\n",
    "from stocks.models import StockBasic\n",
    "\n",
    "# 配置 Django 环境\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'stock_project.settings')\n",
    "django.setup()\n",
    "\n",
    "# 假设你已经有了这个 DataFrame，名为 df\n",
    "data = {\n",
    "    'ts_code': ['000001.SZ'] * 5396,\n",
    "    'symbol': ['000001'] * 5396,\n",
    "    'name': ['平安银行'] * 5396,\n",
    "    'area': ['深圳'] * 5380 + [None] * 16,\n",
    "    'industry': ['银行'] * 5380 + [None] * 16,\n",
    "    'cnspell': ['PAYH'] * 5396,\n",
    "    'market': ['主板'] * 5396,\n",
    "    'list_date': ['1991-04-03'] * 5396,\n",
    "    'act_name': [None] * 2821 + ['平安银行股份有限公司'] * 2575,\n",
    "    'act_ent_type': [None] * 2821 + ['股份有限公司'] * 2575\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 遍历 DataFrame 并插入数据\n",
    "for index, row in df.iterrows():\n",
    "    try:\n",
    "        # 处理缺失值，将 None 转换为空字符串\n",
    "        area = row['area'] if row['area'] is not None else ''\n",
    "        industry = row['industry'] if row['industry'] is not None else ''\n",
    "        act_name = row['act_name'] if row['act_name'] is not None else ''\n",
    "        act_ent_type = row['act_ent_type'] if row['act_ent_type'] is not None else ''\n",
    "\n",
    "        stock = StockBasic(\n",
    "            ts_code=row['ts_code'],\n",
    "            symbol=row['symbol'],\n",
    "            name=row['name'],\n",
    "            area=area,\n",
    "            industry=industry,\n",
    "            list_date=row['list_date']\n",
    "        )\n",
    "        stock.save()\n",
    "        print(f\"第 {index + 1} 行数据保存成功\")\n",
    "    except Exception as e:\n",
    "        print(f\"保存第 {index + 1} 行数据时出错: {e}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   count(*)\n",
      "0      5396\n",
      "     ts_code  symbol  name area industry cnspell market list_date  \\\n",
      "0  000006.SZ  000006  深振业A   深圳     区域地产    szya     主板  19920427   \n",
      "\n",
      "             act_name act_ent_type  \n",
      "0  深圳市人民政府国有资产监督管理委员会         地方国企  \n"
     ]
    }
   ],
   "source": [
    "sql = \"select count(*) from stock_basic.stock_basic\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)\n",
    "\n",
    "sql = \"select * from stock_basic.stock_basic where name='深振业Ａ'\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         ts_code trade_date   open   high    low  close  pre_close  change  \\\n",
      "0      000006.SZ   20250124   6.89   7.10   6.74   7.05       6.88    0.17   \n",
      "1      000006.SZ   20250123   7.16   7.31   6.88   6.88       7.03   -0.15   \n",
      "2      000006.SZ   20250122   7.10   7.35   6.98   7.03       7.58   -0.55   \n",
      "3      000006.SZ   20250121   7.36   8.23   7.25   7.58       7.48    0.10   \n",
      "4      000006.SZ   20250120   6.90   7.48   6.70   7.48       6.80    0.68   \n",
      "...          ...        ...    ...    ...    ...    ...        ...     ...   \n",
      "23995  000006.SZ   19990707  12.05  12.55  11.98  12.50      12.05    0.45   \n",
      "23996  000006.SZ   19990706  12.70  12.90  11.95  12.05      12.97   -0.92   \n",
      "23997  000006.SZ   19990705  13.60  13.90  12.90  12.97      13.46   -0.49   \n",
      "23998  000006.SZ   19990702  13.00  13.62  12.50  13.46      13.09    0.37   \n",
      "23999  000006.SZ   19990701  14.30  14.30  13.09  13.09      14.54   -1.45   \n",
      "\n",
      "       pct_chg        vol       amount  \n",
      "0       2.4709  403265.04  279512.8840  \n",
      "1      -2.1337  453150.36  322820.7120  \n",
      "2      -7.2559  559390.23  399372.3160  \n",
      "3       1.3369  928768.09  724160.0170  \n",
      "4      10.0000  400798.22  291054.6540  \n",
      "...        ...        ...          ...  \n",
      "23995   3.7300   19456.00   23844.4950  \n",
      "23996  -7.0900   25810.00   31891.8762  \n",
      "23997  -3.6400   28153.00   37599.4529  \n",
      "23998   2.8300   47510.00   62826.5586  \n",
      "23999  -9.9700   43904.00   58661.9704  \n",
      "\n",
      "[24000 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "sql = \"select * from stock_basic.`000006.sz`\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据写入成功\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "try:\n",
    "    engine = create_engine('mysql+pymysql://root:limuze@localhost/limuze')\n",
    "    # 假设 data 是有效的 DataFrame\n",
    "    data = pd.DataFrame({\n",
    "        'col1': [1, 2, 3],\n",
    "        'col2': ['a', 'b', 'c']\n",
    "    })\n",
    "    data.to_sql('limuze', con=engine, if_exists='replace', index=False)\n",
    "    print(\"数据写入成功\")\n",
    "except Exception as e:\n",
    "    print(f\"写入数据时出错: {e}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
